
package com.uit.ecommerce.access.mapper;

import com.uit.ecommerce.dto.WeatherAreaDTO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class WeatherAreaMapper extends MapperDB {
    public WeatherAreaMapper() throws Exception{
        super();
    }
 private void InitWeatherAreaDTOFromRs(WeatherAreaDTO WeatherArea, ResultSet rs) throws Exception{
        if((rs != null) && (WeatherArea != null)){
           WeatherArea.setAreaId(rs.getString("MaKhuVuc"));
           WeatherArea.setAreaName(rs.getString("KhuVuc"));
           WeatherArea.setDate(rs.getString("Ngay"));
           WeatherArea.setTempMin(Float.parseFloat(rs.getString("NhietDoThapNhat")));
           WeatherArea.setTempMax(Float.parseFloat(rs.getString("NhietDoCaoNhat")));
           WeatherArea.setNote(rs.getString("ThongTin"));
           WeatherArea.setWeather(rs.getString("DuBao"));
           
        }
    }
     public ArrayList<WeatherAreaDTO> GetAllWeatherArea() throws Exception{
        ArrayList<WeatherAreaDTO> listResult = new ArrayList<WeatherAreaDTO>();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select khuvuc.makhuvuc,  khuvuc, Ngay, NhietDoCaoNhat, NhietDoThapNhat,ThongTin,DuBao from ecormerce.khuvuc, ecormerce.thoitietkhuvuc where khuvuc.MaKhuVuc=thoitietkhuvuc.MaKhuVuc");
             PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
           while((rs != null) && (rs.next())){
                WeatherAreaDTO temp = new WeatherAreaDTO();
                InitWeatherAreaDTOFromRs(temp, rs);
                listResult.add(temp);
            }
        }catch(Exception ex){
            throw ex;
        }

        return listResult;
    }
     public void insertWeatherArea(WeatherAreaDTO cls) throws Exception{
    try{
            StringBuffer sql = new StringBuffer();
            sql.append("Insert into ecormerce.thoitietkhuvuc values('"+cls.getAreaId()+"','"+cls.getDate()+"',"+cls.getTempMax()+","+cls.getTempMin()+",'"+cls.getNote()+"','"+cls.getWeather()+"')");
             PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.execute();
            stmt.close();
        }catch(Exception ex){
            throw ex;
        }
}
public void DeleteWeatherArea(WeatherAreaDTO cls) throws Exception{
    try{
            StringBuffer sql = new StringBuffer();
            sql.append("Delete from ecormerce.thoitietkhuvuc where MaKhuVuc='");
            sql.append(cls.getAreaId()).append("' and Ngay='"+cls.getDate()+"'");
             PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.execute();
            stmt.close();
        }catch(Exception ex){
            throw ex;
        }
}
public boolean  checkWeatherAreaExit(WeatherAreaDTO cls) throws Exception{
     try{
            StringBuffer sql = new StringBuffer();
            sql.append("select * from ecormerce.thoitietkhuvuc where MaKhuVuc='");
            sql.append(cls.getAreaId()).append("' and Ngay='"+cls.getDate()+"'");
             PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
           if((rs != null) && (rs.next())){
                return true;//đã tồn tại
            }
        }catch(Exception ex){
            throw ex;
        }
     return  false;//chưa tồn tại
}
public ArrayList<WeatherAreaDTO> GetAllWeatherAreaByArea(String Area) throws Exception{
        ArrayList<WeatherAreaDTO> listResult = new ArrayList<WeatherAreaDTO>();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select khuvuc.makhuvuc,  khuvuc, Ngay, NhietDoCaoNhat, NhietDoThapNhat,ThongTin,DuBao from ecormerce.khuvuc, ecormerce.thoitietkhuvuc where khuvuc.MaKhuVuc=thoitietkhuvuc.MaKhuVuc and KhuVuc='"+Area+"'");
             PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
           while((rs != null) && (rs.next())){
                WeatherAreaDTO temp = new WeatherAreaDTO();
                InitWeatherAreaDTOFromRs(temp, rs);
                listResult.add(temp);
            }
        }catch(Exception ex){
            throw ex;
        }

        return listResult;
    }
public ArrayList<WeatherAreaDTO> GetAllWeatherAreaByID(String Id) throws Exception{
        ArrayList<WeatherAreaDTO> listResult = new ArrayList<WeatherAreaDTO>();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select khuvuc.makhuvuc,  khuvuc, Ngay, NhietDoCaoNhat, NhietDoThapNhat,ThongTin,DuBao from ecormerce.khuvuc, ecormerce.thoitietkhuvuc where khuvuc.MaKhuVuc=thoitietkhuvuc.MaKhuVuc and khuvuc.makhuvuc='"+Id+"'");
             PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
           while((rs != null) && (rs.next())){
                WeatherAreaDTO temp = new WeatherAreaDTO();
                InitWeatherAreaDTOFromRs(temp, rs);
                listResult.add(temp);
            }
        }catch(Exception ex){
            throw ex;
        }

        return listResult;
    }
public ArrayList<WeatherAreaDTO> GetAllWeatherAreaByDate(String Date) throws Exception{
        ArrayList<WeatherAreaDTO> listResult = new ArrayList<WeatherAreaDTO>();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select khuvuc.makhuvuc,  khuvuc, Ngay, NhietDoCaoNhat, NhietDoThapNhat,ThongTin,DuBao from ecormerce.khuvuc, ecormerce.thoitietkhuvuc where khuvuc.MaKhuVuc=thoitietkhuvuc.MaKhuVuc and Ngay='"+Date+"'");
             PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
           while((rs != null) && (rs.next())){
                WeatherAreaDTO temp = new WeatherAreaDTO();
                InitWeatherAreaDTOFromRs(temp, rs);
                listResult.add(temp);
            }
        }catch(Exception ex){
            throw ex;
        }

        return listResult;
    }
public WeatherAreaDTO GetWeatherAreaByDateAndName(String Date,String name) throws Exception{
       WeatherAreaDTO Result = new WeatherAreaDTO();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select khuvuc.makhuvuc,  khuvuc, Ngay, NhietDoCaoNhat, NhietDoThapNhat,ThongTin,DuBao from ecormerce.khuvuc, ecormerce.thoitietkhuvuc where khuvuc.MaKhuVuc=thoitietkhuvuc.MaKhuVuc and Ngay='"+Date+"' and KhuVuc='"+name+"'");
             PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
           while((rs != null) && (rs.next())){
               InitWeatherAreaDTOFromRs(Result, rs);
            }
        }catch(Exception ex){
            throw ex;
        }

        return Result;
    }
public WeatherAreaDTO GetWeatherAreaMinTemp(String areaCode) throws Exception{
        WeatherAreaDTO Result = new WeatherAreaDTO();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select khuvuc.makhuvuc,  khuvuc, Ngay, NhietDoCaoNhat, NhietDoThapNhat,ThongTin,DuBao from ecormerce.khuvuc, ecormerce.thoitietkhuvuc where khuvuc.MaKhuVuc=thoitietkhuvuc.MaKhuVuc and KhuVuc.MaKhuVuc='"+areaCode+"' and thoitietkhuvuc.NhietDoCaoNhat=(select Max(NhietDoCaoNhat) from ecormerce.thoitietkhuvuc where MaKhuVuc='"+areaCode+"')");
             PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
           while((rs != null) && (rs.next())){
               InitWeatherAreaDTOFromRs(Result, rs);
            }
        }catch(Exception ex){
            throw ex;
        }

        return Result;
    }
public WeatherAreaDTO GetWeatherAreaMaxTemp(String areaCode) throws Exception{
        WeatherAreaDTO Result = new WeatherAreaDTO();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select khuvuc.makhuvuc,  khuvuc, Ngay, NhietDoCaoNhat, NhietDoThapNhat,ThongTin,DuBao from ecormerce.khuvuc, ecormerce.thoitietkhuvuc where khuvuc.MaKhuVuc=thoitietkhuvuc.MaKhuVuc and KhuVuc.MaKhuVuc='"+areaCode+"' and thoitietkhuvuc.NhietDoCaoNhat=(select Max(NhietDoCaoNhat) from ecormerce.thoitietkhuvuc where MaKhuVuc='"+areaCode+"')");
             PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
           while((rs != null) && (rs.next())){
               InitWeatherAreaDTOFromRs(Result, rs);
            }
        }catch(Exception ex){
            throw ex;
        }

        return Result;
    }
}
